package com.mhs.DAO;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.mhs.DTO.ThuocDTO;
import com.mhs.DTO.MySQLiteHelper;
import com.mhs.dataclassification.processK.ProcessK_ThuocDTO;
import com.mhs.dataclassification.processSize.ProcessSize_ThuocDTO;
import com.mhs.dataclassification.processTS.ProcessTS_ThuocDTO;

public class ThuocDAO {
	private static final String TAG = ThuocDAO.class.getName();
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;

	public ThuocDAO(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}
	
	public void close() {
		dbHelper.close();
	}

	public List<ThuocDTO> timKiemThuocDAO(String keyWord, int typeSearch){
		List<ThuocDTO> lstThuoc = new ArrayList<ThuocDTO>();
		try{
			//String sTypeSearch = "";
			String sQuery = 
			"select t.mathuoc, t.maloaithuoc, t.hinhanh, t.tenthuoc, t.sodangky, t.thanhphan, t.dangbaoche, t.donggoi" +
					", t.congtysanxuat, t.chidinh, t.chongchidinh, t.tuongtacthuoc, t.chuydephong" +
					", t.tacdungphu, t.lieuluong, t.nhanbiet, t.baoquan, t.mathuocclient " +
			" from phanloaithuoc plt, thuoc t " +
			" where plt.maloaithuoc = t.maloaithuoc and lock = 0 ";
			switch(typeSearch){
			case 0: // tenthuoc
				sQuery += " and t.tenthuoc like '%" + keyWord + "%';";
				break;
			case 1: // thanhphan
				sQuery += " and t.thanhphan like '%" + keyWord + "%';";
				break;
			case 2: // chidinh
				sQuery += " and t.chidinh like '%" + keyWord + "%';";
				break;
			case 3: // congtysanxuat
				sQuery += " and t.congtysanxuat like '%" + keyWord + "%';";
				break;
			case 4: // sodangky
				sQuery += " and t.sodangky like '%" + keyWord + "%';";
				break;
				default:
					break;
			}
			Log.i(TAG, "query = " + sQuery);
			database = dbHelper.getWritableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			// check cursor co null hay ko
			if(cursor.getCount() == 0){
				lstThuoc = new ArrayList<ThuocDTO>();
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					ThuocDTO thuoc = cursorToThuocDTO(cursor);
					lstThuoc.add(thuoc);			
					ProcessK_ThuocDTO.updateK_ThuocDTO(thuoc.getMATHUOC_CLIENT(), database, dbHelper);
					ProcessTS_ThuocDTO.updateTS_ThuocDTO_Select(thuoc.getMATHUOC_CLIENT(), database, dbHelper);
					cursor.moveToNext();
				}
				// Make sure to close the cursor
				cursor.close();
			}
		}catch(SQLException ex){
			ex.printStackTrace();
			lstThuoc = new ArrayList<ThuocDTO>();
		}finally{
			database.close();
		}
		return lstThuoc;
	}

	private ThuocDTO cursorToThuocDTO(Cursor cursor) {
		ThuocDTO thuocDTO = new ThuocDTO();
//		t.mathuoc, t.maloaithuoc, t.hinhanh, t.tenthuoc, t.sodangky, t.thanhphan, t.dangbaoche, t.donggoi" +
//		", t.congtysanxuat, t.chidinh, t.chongchidinh, t.tuongtacthuoc, t.chuydephong" +
//		", t.tacdungphu, t.lieuluong, t.nhanbiet, t.baoquan
		thuocDTO.setMATHUOC(cursor.getInt(0));
		thuocDTO.setMALOAITHUOC(cursor.getInt(1));
		thuocDTO.setHINHANH(cursor.getString(2));
		thuocDTO.setTENTHUOC(cursor.getString(3));
		thuocDTO.setSODANGKY(cursor.getString(4));
		thuocDTO.setTHANHPHAN(cursor.getString(5));
		thuocDTO.setDANGBAOCHE(cursor.getString(6));
		thuocDTO.setDONGGOI(cursor.getString(7));
		thuocDTO.setCONGTYSANXUAT(cursor.getString(8));
		thuocDTO.setCHIDINH(cursor.getString(9));
		thuocDTO.setCHONGCHIDINH(cursor.getString(10));
		thuocDTO.setTUONGTACTHUOC(cursor.getString(11));
		thuocDTO.setCHUYDEPHONG(cursor.getString(12));
		thuocDTO.setTACDUNGPHU(cursor.getString(13));
		thuocDTO.setLIEULUONG(cursor.getString(14));
		thuocDTO.setNHANBIET(cursor.getString(15));
		thuocDTO.setBAOQUAN(cursor.getString(16));
		thuocDTO.setMATHUOC_CLIENT(cursor.getInt(17));
		return thuocDTO;
	}
	
	public boolean checkThuocExistAndInsert(ThuocDTO t){
		boolean resultCheckAndInsert = false;
		String sSoDangKyThuoc = t.getSODANGKY();
		String sQueryCheck = "select t.mathuoc from thuoc t where t.sodangky = '" + sSoDangKyThuoc + "';";
		database = dbHelper.getReadableDatabase();
		Cursor cursor = database.rawQuery(sQueryCheck, new String [] {});
		if(cursor.getCount() == 0){
			// calculate size ThuocDTO
			int sizeRecord = ProcessSize_ThuocDTO.calculateSize_ThuocDTO_Insert(t);
			// calculate TS
			Long TS = ProcessTS_ThuocDTO.getCurrentTime();
			Log.i(TAG, "TS = " + TS);
			// calculate TTL: don vi tinh: ngay
			int TTL = 7;			
			// Insert thuoc
			String sQueryInsert = "INSERT INTO thuoc(mathuocclient, mathuoc, maloaithuoc, hinhanh, tenthuoc, sodangky, thanhphan," +
				" dangbaoche, donggoi, congtysanxuat, chidinh, chongchidinh, tuongtacthuoc, chuydephong," +
				" tacdungphu, lieuluong, nhanbiet, baoquan, K, size, TS, TTL,lock) " +
				" VALUES (null, " + t.getMATHUOC() + ", " + t.getMALOAITHUOC() + ", '" + t.getHINHANH()+ "', '" + t.getTENTHUOC() + "', " +
				" '" + t.getSODANGKY() + "', '" + t.getTHANHPHAN() + "', " +
				" '" + t.getDANGBAOCHE() + "', '" + t.getDONGGOI() + "', " +
				" '" + t.getCONGTYSANXUAT() + "', '" + t.getCHIDINH() + "', '" + t.getCHONGCHIDINH() + "', " +
				" '" + t.getTUONGTACTHUOC() + "', '" + t.getCHUYDEPHONG() + "', '" + t.getTACDUNGPHU() + "', " +
				" '" + t.getLIEULUONG() + "', '" + t.getNHANBIET() + "', '" + t.getBAOQUAN() + "', 0, " + sizeRecord + ", " + TS.toString() + ", " + TTL + ", 0);";
			Log.i(TAG, "queryInsert = " + sQueryInsert);
			try{
				database.execSQL(sQueryInsert);
				resultCheckAndInsert = true;
			}catch(SQLException ex){
				ex.printStackTrace();
				resultCheckAndInsert = false;
			}finally{
				database.close();
			}
			// true because thuocDTO is inserted successfully
		}else{
			// neu da co thuoc do trong csdl -> update thuoc
			// resultCheckAndInsert = false;
			int sizeRecord = ProcessSize_ThuocDTO.calculateSize_ThuocDTO_Insert(t);
			// calculate TS
			Long TS = ProcessTS_ThuocDTO.getCurrentTime();
			// calculate TTL: don vi tinh: ngay
			int TTL = 7;
			// get K
			int K = ProcessK_ThuocDTO.getK_ThuocDTO(t.getMATHUOC(), database, dbHelper);
			String sQueryUpdate = 	"update thuoc set hinhanh = '" + t.getHINHANH() + "', tenthuoc='" + t.getTENTHUOC() + "', sodangky ='" + t.getSODANGKY() + "', " +
									" thanhphan = '" + t.getTHANHPHAN() + "', dangbaoche = '" + t.getDANGBAOCHE() + "', donggoi = '" + t.getDONGGOI() + "', congtysanxuat = '" + t.getCONGTYSANXUAT() + "', chidinh = '" + t.getCHIDINH() + "', " +
									" chongchidinh = '" + t.getCHONGCHIDINH() + "', tuongtacthuoc = '" + t.getTUONGTACTHUOC() + "', chuydephong = '" + t.getCHUYDEPHONG() + "', tacdungphu = '" + t.getTACDUNGPHU() + "', lieuluong = '" + t.getLIEULUONG() + "', " +
									" nhanbiet = '" + t.getNHANBIET() + "', baoquan = '" + t.getBAOQUAN() + "', K = " + K + ", size = " + sizeRecord + ", TS = '" + TS.toString() + "', TTL = " + TTL + " " +
									" ,lock = 0 where mathuoc = " + t.getMATHUOC() + " and lock = 0;";

			Log.i(TAG, "sQueryUpdate = " + sQueryUpdate);
			try{
				database.execSQL(sQueryUpdate);
				resultCheckAndInsert = true;
			}catch(SQLException ex){
				ex.printStackTrace();
				resultCheckAndInsert = false;
			}finally{
				database.close();
			}
		}
		return resultCheckAndInsert;
	}

	public List<ThuocDTO> getAllThuocDTO() {
		List<ThuocDTO> lstThuoc = new ArrayList<ThuocDTO>();
		try{
			String sQuery = "select mathuoc, maloaithuoc, K, size, TS, TTL , mathuocclient from thuoc where lock = 0 order by K desc;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0){
				lstThuoc = null;
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					ThuocDTO thuoc = new ThuocDTO();
					thuoc.setMATHUOC(cursor.getInt(0));
					thuoc.setMALOAITHUOC(cursor.getInt(1));
					thuoc.setK_ThuocDTO(cursor.getInt(2));
					thuoc.setSize_ThuocDTO(cursor.getInt(3));
					thuoc.setTS_ThuocDTO(new Long(cursor.getString(4)));
					thuoc.setTTL_ThuocDTO(cursor.getInt(5));
					thuoc.setMATHUOC_CLIENT(cursor.getInt(6));
					lstThuoc.add(thuoc);
					cursor.moveToNext();
				}
				cursor.close();
			}
		}catch(SQLException ex){
			ex.printStackTrace();
			lstThuoc = new ArrayList<ThuocDTO>();
		}finally{
			database.close();
		}
		return lstThuoc;
	}

	public boolean delThuocDTO(Integer idThuoc) {
		boolean resultDelete = true;
		String sQueryDel = "delete from thuoc where mathuocclient = " + idThuoc + " and lock = 0;";
		database = dbHelper.getWritableDatabase();
		try{
			database.execSQL(sQueryDel);
			resultDelete = true;
		}catch(SQLException ex){
			ex.printStackTrace();
			resultDelete = false;
		}finally{
			database.close();
		}	
		return resultDelete;
	}

	public boolean checkExistId(int ma) {
		boolean resultCheck = false;
		try{
			String sQueryCheck = "select t.mathuoc from thuoc t where t.mathuoc = " + ma + " and lock = 0;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQueryCheck, new String [] {});
			if(cursor.getCount() == 0){
				resultCheck = false;
			}else{
				resultCheck = true;
			}
		}catch(SQLException ex){
			ex.printStackTrace();
			resultCheck = false;
		}finally{
			database.close();
		}	
		return resultCheck;
	}

	public boolean updateThuocSync(ThuocDTO t) {
		boolean updateThuocSync = true;
		database = dbHelper.getWritableDatabase();
		int sizeRecord = ProcessSize_ThuocDTO.calculateSize_ThuocDTO_Insert(t);
		// calculate TS
		Long TS = ProcessTS_ThuocDTO.getCurrentTime();
		// calculate TTL: don vi tinh: ngay
		int TTL = 7;
		// get K
		int K = ProcessK_ThuocDTO.getK_ThuocDTO(t.getMATHUOC(), database, dbHelper);
		String sQueryUpdate = 	"update thuoc set hinhanh = '" + t.getHINHANH() + "', tenthuoc='" + t.getTENTHUOC() + "', sodangky ='" + t.getSODANGKY() + "', " +
								" thanhphan = '" + t.getTHANHPHAN() + "', dangbaoche = '" + t.getDANGBAOCHE() + "', donggoi = '" + t.getDONGGOI() + "', congtysanxuat = '" + t.getCONGTYSANXUAT() + "', chidinh = '" + t.getCHIDINH() + "', " +
								" chongchidinh = '" + t.getCHONGCHIDINH() + "', tuongtacthuoc = '" + t.getTUONGTACTHUOC() + "', chuydephong = '" + t.getCHUYDEPHONG() + "', tacdungphu = '" + t.getTACDUNGPHU() + "', lieuluong = '" + t.getLIEULUONG() + "', " +
								" nhanbiet = '" + t.getNHANBIET() + "', baoquan = '" + t.getBAOQUAN() + "', K = " + K + ", size = " + sizeRecord + ", TS = '" + TS.toString() + "', TTL = " + TTL + " " +
								" ,lock = 0 where mathuoc = " + t.getMATHUOC() + " and lock = 1;";

		Log.i(TAG, "sQueryUpdate = " + sQueryUpdate);
		try{
			database.execSQL(sQueryUpdate);
			updateThuocSync = true;
		}catch(SQLException ex){
			ex.printStackTrace();
			updateThuocSync = false;
		}finally{
			database.close();
		}		
		return updateThuocSync;
	}

	public boolean inserthuocSync(ThuocDTO t) {
		boolean insertThuocSync = true;
		database = dbHelper.getWritableDatabase();
		int sizeRecord = ProcessSize_ThuocDTO.calculateSize_ThuocDTO_Insert(t);
		// calculate TS
		Long TS = ProcessTS_ThuocDTO.getCurrentTime();
		// calculate TTL: don vi tinh: ngay
		int TTL = 7;
		// Insert thuoc
		String sQueryInsert = "INSERT INTO thuoc(mathuocclient, mathuoc, maloaithuoc, hinhanh, tenthuoc, sodangky, thanhphan," +
			" dangbaoche, donggoi, congtysanxuat, chidinh, chongchidinh, tuongtacthuoc, chuydephong," +
			" tacdungphu, lieuluong, nhanbiet, baoquan, K, size, TS, TTL, lock) " +
			" VALUES (null, " + t.getMATHUOC() + ", " + t.getMALOAITHUOC() + ", '" + t.getHINHANH()+ "', '" + t.getTENTHUOC() + "', " +
			" '" + t.getSODANGKY() + "', '" + t.getTHANHPHAN() + "', " +
			" '" + t.getDANGBAOCHE() + "', '" + t.getDONGGOI() + "', " +
			" '" + t.getCONGTYSANXUAT() + "', '" + t.getCHIDINH() + "', '" + t.getCHONGCHIDINH() + "', " +
			" '" + t.getTUONGTACTHUOC() + "', '" + t.getCHUYDEPHONG() + "', '" + t.getTACDUNGPHU() + "', " +
			" '" + t.getLIEULUONG() + "', '" + t.getNHANBIET() + "', '" + t.getBAOQUAN() + "', 0, " + sizeRecord + ", " + TS.toString() + ", " + TTL + ", 0);";
		Log.i(TAG, "sQueryUpdate = " + sQueryInsert);
		try{
			database.execSQL(sQueryInsert);
			insertThuocSync = true;
		}catch(SQLException ex){
			ex.printStackTrace();
			insertThuocSync = false;
		}finally{
			database.close();
		}		
		return insertThuocSync;
	}

	public boolean deleteThuocSync(ThuocDTO thuocDTO) {
		boolean resultDelete = true;
		String sQueryDel = "delete from thuoc where mathuoc = " + thuocDTO.getMATHUOC() + " and lock = 1;";
		database = dbHelper.getWritableDatabase();
		try{
			database.execSQL(sQueryDel);
			resultDelete = true;
		}catch(SQLException ex){
			ex.printStackTrace();
			resultDelete = false;
		}finally{
			database.close();
		}		
		return resultDelete;
	}

	public List<ThuocDTO> getAllInfoThuocDTO() {
		List<ThuocDTO> lstThuoc = new ArrayList<ThuocDTO>();
		try{
			String sQuery = "select mathuocclient, mathuoc, maloaithuoc, hinhanh, tenthuoc, sodangky, thanhphan," +
					" dangbaoche, donggoi, congtysanxuat, chidinh, chongchidinh, tuongtacthuoc, chuydephong," +
					" tacdungphu, lieuluong, nhanbiet, baoquan from thuoc where lock = 0 order by K DESC ;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0){
				lstThuoc = null;
			}else{
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					ThuocDTO thuocDTO = new ThuocDTO();
					thuocDTO.setMATHUOC_CLIENT(cursor.getInt(0));
					thuocDTO.setMATHUOC(cursor.getInt(1));
					thuocDTO.setMALOAITHUOC(cursor.getInt(2));
					thuocDTO.setHINHANH(cursor.getString(3));
					thuocDTO.setTENTHUOC(cursor.getString(4));
					thuocDTO.setSODANGKY(cursor.getString(5));
					thuocDTO.setTHANHPHAN(cursor.getString(6));
					thuocDTO.setDANGBAOCHE(cursor.getString(7));
					thuocDTO.setDONGGOI(cursor.getString(8));
					thuocDTO.setCONGTYSANXUAT(cursor.getString(9));
					thuocDTO.setCHIDINH(cursor.getString(10));
					thuocDTO.setCHONGCHIDINH(cursor.getString(11));
					thuocDTO.setTUONGTACTHUOC(cursor.getString(12));
					thuocDTO.setCHUYDEPHONG(cursor.getString(13));
					thuocDTO.setTACDUNGPHU(cursor.getString(14));
					thuocDTO.setLIEULUONG(cursor.getString(15));
					thuocDTO.setNHANBIET(cursor.getString(16));
					thuocDTO.setBAOQUAN(cursor.getString(17));
					lstThuoc.add(thuocDTO);
					ProcessK_ThuocDTO.updateK_ThuocDTO(thuocDTO.getMATHUOC_CLIENT(), database, dbHelper);
					ProcessTS_ThuocDTO.updateTS_ThuocDTO_Select(thuocDTO.getMATHUOC_CLIENT(), database, dbHelper);
					cursor.moveToNext();
					
				}
				cursor.close();
			}
		}catch(Exception ex){
			lstThuoc = new ArrayList<ThuocDTO>();
			ex.printStackTrace();
		}finally{
			database.close();
		}
		return lstThuoc;
	}

	public ThuocDTO getThuocFromMaThuoc(int maThuoc) {
		ThuocDTO thuoc = new ThuocDTO();
		try{
			String sQuery = "select mathuocclient, mathuoc, maloaithuoc, hinhanh, tenthuoc, sodangky, thanhphan," +
					" dangbaoche, donggoi, congtysanxuat, chidinh, chongchidinh, tuongtacthuoc, chuydephong," +
					" tacdungphu, lieuluong, nhanbiet, baoquan from thuoc where mathuoc = " + maThuoc + " and lock = 0;";
			database = dbHelper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sQuery, new String [] {});
			if(cursor.getCount() == 0){
				thuoc = null;
			}else if(cursor.getCount() == 1){
				cursor.moveToFirst();
				while (!cursor.isAfterLast()) {
					thuoc.setMATHUOC_CLIENT(cursor.getInt(0));
					thuoc.setMATHUOC(cursor.getInt(1));
					thuoc.setMALOAITHUOC(cursor.getInt(2));
					thuoc.setHINHANH(cursor.getString(3));
					thuoc.setTENTHUOC(cursor.getString(4));
					thuoc.setSODANGKY(cursor.getString(5));
					thuoc.setTHANHPHAN(cursor.getString(6));
					thuoc.setDANGBAOCHE(cursor.getString(7));
					thuoc.setDONGGOI(cursor.getString(8));
					thuoc.setCONGTYSANXUAT(cursor.getString(9));
					thuoc.setCHIDINH(cursor.getString(10));
					thuoc.setCHONGCHIDINH(cursor.getString(11));
					thuoc.setTUONGTACTHUOC(cursor.getString(12));
					thuoc.setCHUYDEPHONG(cursor.getString(13));
					thuoc.setTACDUNGPHU(cursor.getString(14));
					thuoc.setLIEULUONG(cursor.getString(15));
					thuoc.setNHANBIET(cursor.getString(16));
					thuoc.setBAOQUAN(cursor.getString(17));
					ProcessK_ThuocDTO.updateK_ThuocDTO(thuoc.getMATHUOC_CLIENT(), database, dbHelper);
					ProcessTS_ThuocDTO.updateTS_ThuocDTO_Select(thuoc.getMATHUOC_CLIENT(), database, dbHelper);
					break;
				}
				cursor.close();
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			database.close();
		}
		return thuoc;
	}

	public boolean lockRecord(int maThuoc) {
		boolean checkLock = false;
		database = dbHelper.getWritableDatabase();
		String sQueryUpdate = 	"update thuoc set lock = 1 where mathuoc = " + maThuoc + " and lock = 0;";
		Log.i(TAG, "sQueryUpdate = " + sQueryUpdate);
		try{
			database.execSQL(sQueryUpdate);
			checkLock = true;
		}catch(SQLException ex){
			ex.printStackTrace();
			checkLock = false;
		}finally{
			database.close();
		}		
		return checkLock;
	}

}
